package com.imut.lagain.repository;
import org.apache.ibatis.annotations.Select;
import org.slf4j.LoggerFactory;

import com.imut.lagain.entity.BreathingExercise;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.Modifying;


import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 呼吸练习数据访问层
 */
@Mapper
public interface BreathingExerciseRepository extends BaseMapper<BreathingExercise> {

    /**
     * 根据用户ID查询呼吸练习记录
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findByUserId(@Param("userId") Long userId);

    /**
     * 根据用户ID和会话ID查询呼吸练习
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.sessionId = :sessionId AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findByUserIdAndSessionId(@Param("userId") Long userId, @Param("sessionId") Long sessionId);

    /**
     * 根据用户ID和练习类型查询
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.exerciseType = :exerciseType AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findByUserIdAndExerciseType(@Param("userId") Long userId, @Param("exerciseType") String exerciseType);

    /**
     * 根据用户ID和状态查询
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.status = :status AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findByUserIdAndStatus(@Param("userId") Long userId, @Param("status") String status);

    /**
     * 根据用户ID和时间范围查询
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.createdAt BETWEEN :startDate AND :endDate AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findByUserIdAndDateRange(@Param("userId") Long userId, 
                                                     @Param("startDate") LocalDateTime startDate, 
                                                     @Param("endDate") LocalDateTime endDate);

    /**
     * 根据练习ID查询
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.id = :exerciseId AND b.isDeleted = false")
    Optional<BreathingExercise> findByExerciseId(@Param("exerciseId") Long exerciseId);

    /**
     * 更新练习进度
     */
    @Modifying
    @Select("UPDATE BreathingExercise b SET b.cyclesCompleted = :cyclesCompleted, b.status = :status, b.updatedAt = :updatedAt WHERE b.id = :exerciseId")
    int updateProgress(@Param("exerciseId") Long exerciseId, 
                      @Param("cyclesCompleted") Integer cyclesCompleted, 
                      @Param("status") String status, 
                      @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 完成练习
     */
    @Modifying
    @Select("UPDATE BreathingExercise b SET b.status = 'completed', b.completedAt = :completedAt, b.anxietyAfter = :anxietyAfter, b.effectivenessRating = :effectivenessRating, b.updatedAt = :updatedAt WHERE b.id = :exerciseId")
    int completeExercise(@Param("exerciseId") Long exerciseId, 
                        @Param("completedAt") LocalDateTime completedAt, 
                        @Param("anxietyAfter") Integer anxietyAfter, 
                        @Param("effectivenessRating") Integer effectivenessRating, 
                        @Param("updatedAt") LocalDateTime updatedAt);

    /**
     * 统计用户练习次数
     */
    @Select("SELECT COUNT(b) FROM BreathingExercise b WHERE b.userId = :userId AND b.isDeleted = false")
    Long countByUserId(@Param("userId") Long userId);

    /**
     * 统计用户完成的练习次数
     */
    @Select("SELECT COUNT(b) FROM BreathingExercise b WHERE b.userId = :userId AND b.status = 'completed' AND b.isDeleted = false")
    Long countCompletedByUserId(@Param("userId") Long userId);

    /**
     * 统计用户各类型练习次数
     */
    @Select("SELECT b.exerciseType, COUNT(b) FROM BreathingExercise b WHERE b.userId = :userId AND b.status = 'completed' AND b.isDeleted = false GROUP BY b.exerciseType")
    List<Object[]> countByUserIdAndExerciseType(@Param("userId") Long userId);

    /**
     * 查询用户平均练习时长
     */
    @Select("SELECT AVG(b.duration) FROM BreathingExercise b WHERE b.userId = :userId AND b.status = 'completed' AND b.isDeleted = false")
    Double getAverageDurationByUserId(@Param("userId") Long userId);

    /**
     * 查询用户平均效果评分
     */
    @Select("SELECT AVG(b.effectivenessRating) FROM BreathingExercise b WHERE b.userId = :userId AND b.effectivenessRating IS NOT NULL AND b.isDeleted = false")
    Double getAverageEffectivenessRating(@Param("userId") Long userId);

    /**
     * 查询用户平均焦虑改善程度
     */
    @Select("SELECT AVG(b.anxietyBefore - b.anxietyAfter) FROM BreathingExercise b WHERE b.userId = :userId AND b.anxietyBefore IS NOT NULL AND b.anxietyAfter IS NOT NULL AND b.isDeleted = false")
    Double getAverageAnxietyImprovement(@Param("userId") Long userId);

    /**
     * 查询用户最近的练习记录
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findRecentExercisesByUserId(@Param("userId") Long userId, org.springframework.data.domain.Pageable pageable);

    /**
     * 查询用户最喜欢的练习类型
     */
    @Select("SELECT b.exerciseType FROM BreathingExercise b WHERE b.userId = :userId AND b.effectivenessRating >= 4 AND b.isDeleted = false GROUP BY b.exerciseType ORDER BY COUNT(b) DESC")
    List<String> getPreferredExerciseTypes(@Param("userId") Long userId);

    /**
     * 查询未完成的练习
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.userId = :userId AND b.status IN ('pending', 'in_progress') AND b.isDeleted = false ORDER BY b.createdAt DESC")
    List<BreathingExercise> findIncompleteExercises(@Param("userId") Long userId);

    /**
     * 查询超时未完成的练习
     */
    @Select("SELECT b FROM BreathingExercise b WHERE b.status = 'in_progress' AND b.startedAt < :timeoutThreshold AND b.isDeleted = false")
    List<BreathingExercise> findTimeoutExercises(@Param("timeoutThreshold") LocalDateTime timeoutThreshold);
}
